
public class DatabaseTest {
    public static void main (String args[])
    {
        Connection conn = null;
        try
        {
            // Loads the class object for the mysql driver into the DriverManager.
            Class.forName("com.mysql.jdbc.Driver");

            // Attempt to establish a connection to the specified database via the
            // DriverManager
            conn = DriverManager.getConnection("jdbc:mysql://localhost/" +
                    "world?user=root&password=");

            // Check the connection
            if (conn != null)
            {
                System.out.println("We have connected to our database!");

                Statement stmt = conn.createStatement();
                ResultSet result = stmt.executeQuery("SELECT * FROM city WHERE city.Population>2017233");
                System.out.println("查询世界上人口超过的2017233所有城市列表");

                DatabaseTest.showResults("city", result);

                Statement stmt1 = conn.createStatement();
                ResultSet result1 = stmt1.executeQuery("SELECT * FROM country WHERE country.LifeExpectancy=(SELECT MAX(country.LifeExpectancy)FROM country)");

                System.out.println("哪个国家的平均寿命最长");
                DatabaseTest.showResults("country",result1);

                Statement stmt2 = conn.createStatement();
                ResultSet result2 = stmt2.executeQuery("SELECT * FROM country WHERE country.LifeExpectancy=(SELECT MIN(country.LifeExpectancy)FROM country) ");

                System.out.println("哪个国家的平均寿命最短");
                DatabaseTest.showResults("country",result2);


                Statement stmt3 = conn.createStatement();
                ResultSet result3 = stmt3.executeQuery("SELECT SUM(Population) FROM country WHERE country.Region=\"Middle East\"");

                System.out.println();
                System.out.println("查询世界上的所有中东国家的总人口");
                DatabaseTest.showResults("country",result3);

                // Close the database
                conn.close();
            }

        } catch (SQLException ex) {
            System.out.println("SQLException: " + ex.getMessage());
            ex.printStackTrace();

        } catch (Exception ex) {
            System.out.println("Exception: " + ex.getMessage());
            ex.printStackTrace();
        }
    }

    //-----------------------------------------------------------------
    //  Displays the contents of the specified ResultSet.
    //-----------------------------------------------------------------
    public static void showResults(String tableName, ResultSet rSet)
    {
        try
        {
            ResultSetMetaData rsmd = rSet.getMetaData();
            int numColumns = rsmd.getColumnCount();
            String resultString = null;
            if (numColumns > 0)
            {
                resultString = "\nTable: " + tableName + "\n" +
                        "=======================================================\n";
                for (int colNum = 1; colNum <= numColumns; colNum++)
                    resultString += rsmd.getColumnLabel(colNum) + "     ";
            }
            System.out.println(resultString);
            System.out.println(
                    "=======================================================");

            while (rSet.next())
            {
                resultString = "";
                for (int colNum = 1; colNum <= numColumns; colNum++)
                {
                    String column = rSet.getString(colNum);
                    if (column != null)
                        resultString += column + "     ";
                }
                System.out.println(resultString + '\n' +
                        "-------------------------------------------------------");
            }
        } catch (SQLException ex) {
            System.out.println("SQLException: " + ex.getMessage());
            ex.printStackTrace();
        }
    }
}


